/****************************************************************
 * <strong>MFSimpleExcel简单数据交换类</strong>
 *
 * <p>
 *MFSimpleExcel简单数据交换类 
 * </p>
 *
 * @author AndyZhang 2011 cnet2001@163.com
 * @version 0.1
 * ToDo:2011-11-29 支持 BigTable
 ***************************************************************/

package com.thinkwide.data.exchange;

import com.thinkwide.data.ormapping.datatable.MFDataColumn;
import com.thinkwide.data.ormapping.datatable.MFDataColumns;
import com.thinkwide.data.ormapping.datatable.MFDataRow;
import com.thinkwide.data.ormapping.datatable.MFDataTable;
import com.thinkwide.data.core.MFString;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;

public class MFSimpleExcel {
    public static void CreateExcel(OutputStream stream, MFDataTable dt, MFDataColumns columns) {
        try {
            WritableWorkbook book = Workbook.createWorkbook(stream);
            WritableSheet sheet = book.createSheet("第一页", 0);

            WritableCellFormat rowWc = new WritableCellFormat();
            rowWc.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);

            WritableFont titleFont = new WritableFont(WritableFont.createFont("宋体"), 11, WritableFont.BOLD, false);
            titleFont.setColour(Colour.WHITE);

            WritableCellFormat titleWc = new WritableCellFormat(titleFont);
            titleWc.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);

            titleWc.setBackground(Colour.BLUE_GREY);
            sheet.setRowView(0, 400);

            int i = 0;

            for (MFDataColumn item : columns) {
                if (!item.getEnable()) {
                    continue;
                }

                sheet.setColumnView(i, 18);
                Label label = new Label(i, 0, item.getTitle(), titleWc);

                sheet.addCell(label);
                i++;
            }

            int r = 1;
            int j = 0;
            for (MFDataRow row : dt.getRows()) {
                i = 0;
                j = 0;
                for (MFDataColumn col : columns) {
                    if (!col.getEnable()) {
                        j++;
                        continue;
                    }
                    //MFDebug.println(col.getDataType());

                    if (MFString.compareNoCase(col.getDataType(), "DOUBLE") ||
                            MFString.compareNoCase(col.getDataType(), "FLOAT") ||
                            MFString.compareNoCase(col.getDataType(), "INT") ||
                            MFString.compareNoCase(col.getDataType(), "NUMBER")) {
                        if (MFString.notNullOrEmpty(col.getFormat())) {
                            WritableCellFormat rowX = new WritableCellFormat(new NumberFormat(col.getFormat()));
                            rowX.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
                            jxl.write.Number numb = new jxl.write.Number(i, r, row.getFloat(j), rowX);
                            sheet.addCell(numb);
                        } else {
                            jxl.write.Number numb = new jxl.write.Number(i, r, row.getFloat(j), rowWc);
                            sheet.addCell(numb);
                        }

                    } else {
                        Label label = new Label(i, r, row.getString(j), rowWc);
                        sheet.addCell(label);
                    }


                    i++;
                    j++;
                }

                r++;
            }

            //jxl.write.Number number = new jxl.write.Number(1,0,789.123);
            //sheet.addCell(number);

            book.write();
            book.close();
            stream.flush();
            stream.close();
            System.gc();

        } catch (Exception e) {
            System.out.println(e);
        }
    }

    public static void CreateExcel(OutputStream stream, MFDataTable dt) {
        try {
            WritableWorkbook book = Workbook.createWorkbook(stream);
            WritableSheet sheet = book.createSheet("第一页", 0);

            WritableCellFormat rowWc = new WritableCellFormat();
            rowWc.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);

            WritableFont titleFont = new WritableFont(WritableFont.createFont("宋体"), 11, WritableFont.BOLD, false);
            titleFont.setColour(Colour.WHITE);

            WritableCellFormat titleWc = new WritableCellFormat(titleFont);
            titleWc.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);

            titleWc.setBackground(Colour.BLUE_GREY);
            sheet.setRowView(0, 400);

            int i = 0;

            for (MFDataColumn item : dt.getColumns()) {
                if (!item.getEnable()) {
                    continue;
                }

                sheet.setColumnView(i, 18);
                Label label = new Label(i, 0, item.getTitle(), titleWc);

                sheet.addCell(label);
                i++;
            }

            int r = 1;
            int j = 0;
            for (MFDataRow row : dt.getRows()) {
                i = 0;
                j = 0;
                for (MFDataColumn col : dt.getColumns()) {
                    if (!col.getEnable()) {
                        j++;
                        continue;
                    }
                    //MFDebug.println(col.getDataType());

                    if (MFString.compareNoCase(col.getDataType(), "DOUBLE") ||
                            MFString.compareNoCase(col.getDataType(), "FLOAT") ||
                            MFString.compareNoCase(col.getDataType(), "INT") ||
                            MFString.compareNoCase(col.getDataType(), "NUMBER")) {
                        if (MFString.notNullOrEmpty(col.getFormat())) {
                            WritableCellFormat rowX = new WritableCellFormat(new NumberFormat(col.getFormat()));
                            rowX.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
                            jxl.write.Number numb = new jxl.write.Number(i, r, row.getFloat(j), rowX);
                            sheet.addCell(numb);
                        } else {
                            jxl.write.Number numb = new jxl.write.Number(i, r, row.getFloat(j), rowWc);
                            sheet.addCell(numb);
                        }

                    } else {
                        Label label = new Label(i, r, row.getString(j), rowWc);
                        sheet.addCell(label);
                    }


                    i++;
                    j++;
                }

                r++;
            }

            //jxl.write.Number number = new jxl.write.Number(1,0,789.123);
            //sheet.addCell(number);

            book.write();
            book.close();
            stream.flush();
            stream.close();
            System.gc();

        } catch (Exception e) {
            System.out.println(e);
        }
    }


    public static void CreateExcel(String path, MFDataTable dt) throws Exception {
        File file = new File(path);
        file.createNewFile();
        CreateExcel(new FileOutputStream(file), dt);
    }

    public static InputStream LoadExcel(String filePath) throws FileNotFoundException {
        File file = new File(filePath);
        InputStream stream = new FileInputStream(file);
        return stream;
    }

    public static MFDataTable LoadExcel(InputStream stream) {
        MFDataTable dt = new MFDataTable();
        Workbook book = null;
        try {
            book = Workbook.getWorkbook(stream);
            Sheet sheet = book.getSheet(0);

            for (int iCol = 0; iCol < sheet.getColumns(); iCol++) {
                Cell cell = sheet.getCell(iCol, 0);
                String title = cell.getContents();
                dt.addColumn("data" + iCol, title);
            }

            for (int iRow = 1; iRow < sheet.getRows(); iRow++) {
                MFDataRow row = dt.newRow();

                for (int iCol = 0; iCol < sheet.getColumns(); iCol++) {
                    Cell cell = sheet.getCell(iCol, iRow);
                    String data = cell.getContents();
                    row.setValue(iCol, data);
                }

            }

            book.close();
        } catch (Exception e) {
            if (book != null) book.close();
            System.out.println(e);
        }
        return dt;
    }

} 

